-- @owner: @songjing20
-- @date: 2023-1-14
-- @testpoint: desc/describe分析create table as语句

--step1:创建表插入数据;expect:成功
drop table if exists t_describe_0072_01, t_describe_0072_02 cascade;
set dolphin.support_interval_to=on;
create table t_describe_0072_01(c_id integer, c_boolean boolean, c_integer integer, c_bigint bigint,
c_real real, c_decimal decimal(38), c_number number(38), c_char char(50) default null, c_varchar varchar(50),
c_clob clob, c_blob blob, c_timestamp timestamp, c_interval interval day to second);

insert into t_describe_0072_01(c_id) values(123),(456),(789),(654),(321);
insert into t_describe_0072_01(c_boolean) values(true),(false),(10),(0),(null);
insert into t_describe_0072_01(c_integer) values(-100),(100),(-2147483648),(2147483647),(null);
insert into t_describe_0072_01(c_bigint) values(-100),(100),(-9223372036854775808),(9223372036854775807),(null);
insert into t_describe_0072_01(c_real) values(-10.01),(10.01),(-9223372036854.775808),(9223372036.854775807),(null);
insert into t_describe_0072_01(c_decimal) values(-10.01),(10.01),(-9223372036854.775808),(9223372036.854775807),(null);
insert into t_describe_0072_01(c_char) values('abc123456789abc123456789abc123456789abc123456789'),('abc123456789abc123456789abc123456789a'),(null);
insert into t_describe_0072_01(c_varchar) values('abc123456789abc123456789abc123456789abc123456789'),('abc123456789abc123456789abc123456789a'),(null);
insert into t_describe_0072_01(c_clob) values('abc123456789abc123456789abc123456789abc123456789'),('abc123456789abc123456789abc123456789a'),(null);
insert into t_describe_0072_01(c_blob) values('1010101111111111111111111111111111111111111111'),('10101011111111111111111111111111111111111111'),(null);
insert into t_describe_0072_01(c_timestamp) values(TO_DATE('2018-06-28 13:14:15', 'YYYY-MM-DD HH24:MI:SS')),(TO_DATE('2018-JUN-28 01:14:15', 'YYYY-MON-DD HH:MI:SS')),(null);
insert into t_describe_0072_01(c_interval) values('12:3:4'),(null);

--step2:事务中分析create table as 执行计划;expect:打印执行计划
begin;/
desc analyze create table t_describe_0072_02 as select distinct c_id, c_char, c_interval, null add_null from t_describe_0072_01 where c_id < 400;
select * from t_describe_0072_02;
rollback;
begin;/
describe analyze create table t_describe_0072_02 as select distinct c_id, c_char, c_interval, null add_null from t_describe_0072_01 where c_id < 400;
commit;
select * from t_describe_0072_02;

--step3:清理环境;expect:成功
drop table if exists t_describe_0072_01, t_describe_0072_02 cascade;